package com.bsy.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import java.util.Properties;

import javax.servlet.jsp.tagext.IterationTag;

import com.bsy.bean.Paiming;

public class PaimingDao {
	public ArrayList<Paiming>  query() {
		ArrayList<Paiming> paimingArr = new ArrayList<Paiming>();
		//List<String> paimingArr1 = new ArrayList<String>();
		//List<Integer> paimingArr2 = new ArrayList<Integer>();	
		//Map<String, Object> map = new HashMap<String, Object>();
		Connection conn = null;
		Properties properties = new Properties();
		properties.setProperty("user", "root");
		properties.setProperty("password", "123456");
		properties.setProperty("useSSL", "false");
		properties.setProperty("autoReconnect", "true");

		try {
			Class.forName("com.mysql.jdbc.Driver");

		 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bigdata_bsy?useUnicode=true&characterEncoding=utf-8",
					properties);
		 PreparedStatement stmt4 = conn.prepareStatement("SELECT COUNT(*) FROM t_paiming");
			ResultSet rs4 = stmt4.executeQuery();		
			int sum =0;
			while (rs4.next()) {
				 sum = rs4.getInt("COUNT(*)");
				
			}
			
//		 	PreparedStatement stmt3 = conn.prepareStatement("SELECT MAX(idc) FROM t_paiming");
//			ResultSet rs3 = stmt3.executeQuery();
//			int max = rs3.getInt("MAX(dic)");
			PreparedStatement stmt1 = conn.prepareStatement("SELECT num FROM t_paiming  where id = 0");
			ResultSet rs1 = stmt1.executeQuery();
			int inum = 0;
			while(rs1.next()){
				inum = rs1.getInt("num");
			}
			if(inum<=sum){
				String aString = "SELECT * FROM t_paiming where idc=1  order by num desc limit"; 
				String bString = inum+",6";
				String cString = aString+" "+bString;
				PreparedStatement stmt = conn.prepareStatement(cString);
				ResultSet rs = stmt.executeQuery();
				int aa = inum+1;
				while (rs.next()) {
					
					Paiming paiming = new Paiming();
					paiming.setId(aa);
					paiming.setName(rs.getString("name"));
					paiming.setNum(rs.getInt("num"));
					paimingArr.add(paiming);
					aa++;
				}	
				inum = inum+6;
			}else {
				inum = 0;
				String aString = "SELECT * FROM t_paiming where idc=1 order by num desc limit"; 
				String bString = inum+",6";
				String cString = aString+" "+bString;
				PreparedStatement stmt = conn.prepareStatement(cString);
				ResultSet rs = stmt.executeQuery();
				int aa = inum+1;
				while (rs.next()) {
					
					Paiming paiming = new Paiming();
					paiming.setId(aa);
					paiming.setName(rs.getString("name"));
					paiming.setNum(rs.getInt("num"));
					paimingArr.add(paiming);
					aa++;
				}	
				inum=inum+6;			
				}
			
			PreparedStatement stmt2 = conn.prepareStatement("UPDATE  t_paiming  set num='"+inum+"' where id = 0");
			stmt2.executeUpdate();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		/*map.put("name", paimingArr1);
		map.put("num", paimingArr2);*/
		return paimingArr;
	}
}

